package org.ihale.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.ihale.Ihale;
import org.ihale.tedarikci.Tedarikci;
import org.ihale.satinalmaci.SatinAlmaci;

/**
 *
 * @author Emre
 */
public class Database {

    private Connection connection = null;
    private final String driver = "com.mysql.jdbc.Driver";
    private final String connectionString = "jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=UTF-8";
    private final String user = "root";
    private final String password = "1234";

    public void connect() throws ClassNotFoundException, SQLException {
        Class.forName(driver);
        connection = DriverManager.getConnection(connectionString, user, password);
        System.out.println("Jdbc Mysql Connection String :");
        System.out.println(connection);


    }

    /**
     * Disconnection method for disconnecting the database
     * @throws SQLException Disconnect sql exception
     */
    public void disconnect() throws SQLException {
        if ((this.connection != null)) {
            if (!this.connection.isClosed()) {
                this.connection.close();
            }
        }
    }

    public List<Ihale> listeleIhale() {
        List<Ihale> ihaleler = new LinkedList<Ihale>();
        try {
            String query = "Select * from ihale";
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            Ihale ihale;
            java.util.Date baslamaTarihi;
            java.util.Date bitisTarihi;
            String baslangicSaati;
            String bitisSaati;
            SatinAlmaci satinAlmaci = new SatinAlmaci();
            while (rs.next()) {
                Timestamp tba = rs.getTimestamp("baslama_tarihi");
                //baslamaTarihi = new java.util.Date(tba.getYear(),tba.getMonth(),tba.getDate());
                baslamaTarihi = new java.util.Date(tba.getTime());
                Timestamp tbi = rs.getTimestamp("bitis_tarihi");
                // bitisTarihi = new java.util.Date(tbi.getYear(),tbi.getMonth(),tbi.getDate());
                bitisTarihi = new java.util.Date(tbi.getTime());
                String baslangicS = Integer.toString(tba.getHours());
                if(baslangicS.length() < 2)
                    baslangicS="0"+baslangicS;
                
                String baslangicD = Integer.toString(tba.getMinutes());
                if(baslangicD.length() < 2)
                    baslangicD = "0"+baslangicD;
                baslangicSaati = baslangicS + ":" + baslangicD;
                
                String bitisS = Integer.toString(tbi.getHours());
                String bitisD = Integer.toString(tbi.getMinutes());
                if(bitisS.length()<2)
                    bitisS = "0" +bitisS;
                if(bitisD.length()<2)
                    bitisD = "0" +bitisD;
  
                bitisSaati = bitisS + ":" + bitisD;
                satinAlmaci.setId(rs.getInt("sat_almaci_id"));
                satinAlmaci = getirSatinAlmaci(satinAlmaci);
                ihale = new Ihale(rs.getInt("idihale"), rs.getString("aciklama"), baslamaTarihi, bitisTarihi, satinAlmaci, rs.getString("ihale_adi"), baslangicSaati, bitisSaati,rs.getString("belge"));
                ihaleler.add(ihale);
            }


        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ihaleler;

    }

    public void ekleIhale(Ihale ihale) throws SQLException {
        String[] tmp = new String[2];
        tmp = ihale.getBaslangicSaati().split(":");
        String[] tmp2 = new String[2];
        tmp2 = ihale.getBitisSaati().split(":");

        String query = "INSERT INTO ihale(ihale_adi,baslama_tarihi,bitis_tarihi,sat_almaci_id,aciklama,belge) VALUES (?,?,?,?,?,?)";
        PreparedStatement statement = connection.prepareStatement(query);
        java.sql.Timestamp baslangic = new Timestamp(ihale.getBaslamaTarihi().getYear(), ihale.getBaslamaTarihi().getMonth(), ihale.getBaslamaTarihi().getDate(), Integer.parseInt(tmp[0]), Integer.parseInt(tmp[1]), 0, 0);
        java.sql.Timestamp bitis = new Timestamp(ihale.getBitisTarihi().getYear(), ihale.getBitisTarihi().getMonth(), ihale.getBitisTarihi().getDate(), Integer.parseInt(tmp2[0]), Integer.parseInt(tmp2[1]), 0, 0);
        //java.sql.Date baslangic = new Date(ihale.getBaslamaTarihi().getTime());
        //java.sql.Date bitis = new Date(ihale.getBitisTarihi().getTime());
        statement.setString(1, ihale.getAd());
        statement.setTimestamp(2, baslangic);
        statement.setTimestamp(3, bitis);
        statement.setInt(4, ihale.getSatinAlmaci().getId());
        statement.setString(5, ihale.getAciklama());
        statement.setString(6, ihale.getBelge());
        statement.execute();


    }

    public void silIhale(Ihale ihale) throws SQLException {
        String query = "DELETE FROM ihale WHERE idihale =? ";
        PreparedStatement stmet = (PreparedStatement) connection.prepareStatement(query);
        stmet.setInt(1, ihale.getId());
        stmet.executeUpdate();

    }

    public void guncelleIhale(Ihale ihale) {
        String[] tmp = new String[2];
        tmp = ihale.getBaslangicSaati().split(":");
        String[] tmp2 = new String[2];
        tmp2 = ihale.getBitisSaati().split(":");


        java.sql.Timestamp baslangic = new Timestamp(ihale.getBaslamaTarihi().getYear(), ihale.getBaslamaTarihi().getMonth(), ihale.getBaslamaTarihi().getDate(), Integer.parseInt(tmp[0]), Integer.parseInt(tmp[1]), 0, 0);
        java.sql.Timestamp bitis = new Timestamp(ihale.getBitisTarihi().getYear(), ihale.getBitisTarihi().getMonth(), ihale.getBitisTarihi().getDate(), Integer.parseInt(tmp2[0]), Integer.parseInt(tmp2[1]), 0, 0);
        try {
            String query = "UPDATE ihale SET ihale_adi = ?,baslama_tarihi =?,bitis_tarihi = ? , sat_almaci_id = ? , aciklama =? , belge =?  where idihale =?";
            PreparedStatement stmt = connection.prepareStatement(query);

            stmt.setString(1, ihale.getAd());
            stmt.setTimestamp(2, baslangic);
            stmt.setTimestamp(3, bitis);
            stmt.setInt(4, ihale.getSatinAlmaci().getId());
            stmt.setString(5, ihale.getAciklama());
            stmt.setInt(7, ihale.getId());
            stmt.setString(6, ihale.getBelge());
            stmt.executeUpdate();
           
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }


    }

    public SatinAlmaci getirSatinAlmaci(SatinAlmaci satinAlmaci) {
        SatinAlmaci satinAlmaci2 = new SatinAlmaci();
        try {

            String query = "Select * from satinalmaci where idsatinalmaci = ?";
            PreparedStatement stmt = connection.prepareStatement(query);
            stmt.setInt(1, satinAlmaci.getId());
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                satinAlmaci2 = new SatinAlmaci(rs.getInt("idsatinalmaci"), rs.getString("sirket_adi"), rs.getString("adres_tel"), rs.getString("websitesi"), rs.getString("ilgili_kisi"), rs.getString("sifre"), rs.getString("kullanici_adi"), rs.getString("eposta"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        return satinAlmaci2;
    }

    public Map<String, String> listeleSatinAlmacilarMap() {
        Map<String, String> satinAlmaciMap = new HashMap<String, String>();

        try {
            String query = "Select * from satinalmaci";
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            SatinAlmaci satinAlmaci;
            while (rs.next()) {
                //int id, String sirketAdi, String adres, String webSite, String ilgiliKisi, String sifre, String kullaniciAdi, String eposta)
                satinAlmaci = new SatinAlmaci(rs.getInt("idsatinalmaci"), rs.getString("sirket_adi"), rs.getString("adres_tel"), rs.getString("websitesi"), rs.getString("ilgili_kisi"), rs.getString("sifre"), rs.getString("kullanici_adi"), rs.getString("eposta"));
                satinAlmaciMap.put(satinAlmaci.getSirketAdi(), Integer.toString(satinAlmaci.getId()));

            }
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }

        return satinAlmaciMap;

    }

    public boolean adminLogin(String username, String password) {
        return true;
    }

    /********satin almaci fonksiyonları
     * Author Mehmet Sinan
     * 
     */
    public boolean ekleSatinAlmaci(SatinAlmaci satinAlmaci) {
        String query = "INSERT INTO satinalmaci(sirket_adi,adres_tel,websitesi,ilgili_kisi,sifre,kullanici_adi,eposta) VALUES (?,?,?,?,?,?,?)";
        try {
            PreparedStatement statement = connection.prepareStatement(query);

            statement.setString(1, satinAlmaci.getSirketAdi());
            statement.setString(2, satinAlmaci.getAdres());
            statement.setString(3, satinAlmaci.getWebSite());
            statement.setString(4, satinAlmaci.getIlgiliKisi());
            statement.setString(5, satinAlmaci.getSifre());
            statement.setString(6, satinAlmaci.getKullaniciAdi());
            statement.setString(7, satinAlmaci.getEposta());
            statement.executeUpdate();
        } catch (SQLException e) {
            return false;
        }
        return true;
    }

    public List<SatinAlmaci> listeSatinAlmaci() throws SQLException, ClassNotFoundException {
        String query = "SELECT idsatinalmaci, sirket_adi,adres_tel,websitesi,ilgili_kisi,kullanici_adi,eposta FROM  satinalmaci";
        List<SatinAlmaci> satinalmaciListesi = new ArrayList<SatinAlmaci>();
        try {
            Statement statement = this.connection.createStatement();
            ResultSet result = statement.executeQuery(query);
            while (result.next()) {
                SatinAlmaci satinAlmaci = new SatinAlmaci();
                satinAlmaci.setId(result.getInt("idsatinalmaci"));
                satinAlmaci.setSirketAdi(result.getString("sirket_adi"));
                satinAlmaci.setAdres(result.getString("adres_tel"));
                satinAlmaci.setWebSite(result.getString("websitesi"));
                satinAlmaci.setIlgiliKisi(result.getString("ilgili_kisi"));
                satinAlmaci.setKullaniciAdi(result.getString("kullanici_adi"));
                satinAlmaci.setEposta(result.getString("eposta"));
                satinAlmaci.setTedarikciler(listeTedarikci(satinAlmaci.getId()));
                satinalmaciListesi.add(satinAlmaci);
                
            }
            return satinalmaciListesi;
        } catch (SQLException e) {
            return satinalmaciListesi;
        }


    }

    public List<Tedarikci> listeTedarikci(int sat_id) throws SQLException {
        String query = "SELECT * FROM  tedarikci, sat_ted where tedarikci.idtedarikci=sat_ted.ted_id and sat_ted.sa_id=?";
        List<Tedarikci> TedarikciListesi = new ArrayList<Tedarikci>();

        try {
            PreparedStatement commit = this.connection.prepareStatement(query);
            commit.setInt(1, sat_id);
            ResultSet result = commit.executeQuery();
             while (result.next()) {
                Tedarikci tedarikci = new Tedarikci();
                tedarikci.setId(result.getInt("idtedarikci"));
                tedarikci.setSirketAdi(result.getString("sirket_adi"));
                tedarikci.setAdres(result.getString("adres_tel"));
                tedarikci.setWebSite(result.getString("websitesi"));
                tedarikci.setIlgiliKisi(result.getString("ilgili_kisi"));
                tedarikci.setKullaniciAdi(result.getString("kullanici_adi"));
                tedarikci.setEposta(result.getString("eposta"));
                TedarikciListesi.add(tedarikci);
            }
            return TedarikciListesi;
        } catch (SQLException e) {
            return TedarikciListesi;
        }


    }
    
    public SatinAlmaci getirSatinAlmaci(String satinalmaci_id) {
        String query = "SELECT idsatinalmaci, sirket_adi,adres_tel,websitesi,ilgili_kisi,kullanici_adi,eposta FROM  satinalmaci where (idsatinalmaci=?)";
        SatinAlmaci satinAlmaci = new SatinAlmaci();
        try {
            PreparedStatement commit = this.connection.prepareStatement(query);
            commit.setString(1, satinalmaci_id);
            ResultSet result = commit.executeQuery();
            satinAlmaci.setId(result.getInt("idsatinalmaci"));
            satinAlmaci.setSirketAdi(result.getString("sirket_adi"));
            satinAlmaci.setAdres(result.getString("adres_tel"));
            satinAlmaci.setWebSite(result.getString("websitesi"));
            satinAlmaci.setIlgiliKisi(result.getString("ilgili_kisi"));
            satinAlmaci.setKullaniciAdi(result.getString("kullanici_adi"));
            satinAlmaci.setEposta(result.getString("eposta"));
            return satinAlmaci;
        } catch (SQLException e) {
            return satinAlmaci;
        }
    }

    public boolean silSatinAlmaci(SatinAlmaci satinalmaci) {
        String query = "Delete from satinalmaci where (idsatinalmaci=?)";
        try {
            PreparedStatement stmet = (PreparedStatement) connection.prepareStatement(query);
            stmet.setInt(1, satinalmaci.getId());
            stmet.executeUpdate();
            return true;
        } catch (SQLException e) {
            return false;
        }
    }

    public boolean guncelleSatinAlmaci(SatinAlmaci satinalmaci) {
        try {
            String query = "UPDATE satinalmaci SET  sirket_adi=?,adres_tel=?,websitesi=?,ilgili_kisi=? where idsatinalmaci =?";
            PreparedStatement stmt = connection.prepareStatement(query);
            stmt.setString(1, satinalmaci.getSirketAdi());
            stmt.setString(2, satinalmaci.getAdres());
            stmt.setString(3, satinalmaci.getWebSite());
            stmt.setString(4, satinalmaci.getIlgiliKisi());
            stmt.setInt(5, satinalmaci.getId());
            stmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }

    }

    /********tedarikçi fonksiyonları
     * Author Mehmet Sinan
     * 
     */
    public boolean ekleTedarikci(Tedarikci tedarikci,String satinalmaci_id) throws ClassNotFoundException, SQLException {
        String query = "INSERT INTO tedarikci(sirket_adi,adres_tel,websitesi,ilgili_kisi,sifre,kullanici_adi,eposta) VALUES (?,?,?,?,?,?,?)";
        try {
            PreparedStatement statement = connection.prepareStatement(query);

            statement.setString(1, tedarikci.getSirketAdi());
            statement.setString(2, tedarikci.getAdres());
            statement.setString(3, tedarikci.getWebSite());
            statement.setString(4, tedarikci.getIlgiliKisi());
            statement.setString(5, tedarikci.getSifre());
            statement.setString(6, tedarikci.getKullaniciAdi());
            statement.setString(7, tedarikci.getEposta());
            statement.executeUpdate();
        } catch (SQLException e) {
           
            return false;
        }
        this.connection.setAutoCommit(false);
         this.connection.commit();  
        this.connection.setAutoCommit(true);
        
         query = "INSERT INTO sat_ted (sa_id,ted_id) VALUES (?,(SELECT idtedarikci FROM tedarikci WHERE kullanici_adi LIKE ?))";
        try {
            PreparedStatement statement = connection.prepareStatement(query);

            statement.setString(1,satinalmaci_id); 
            statement.setString(2,tedarikci.getEposta()); 
            statement.executeUpdate();
        } 
        catch (SQLException e) 
        {
            query = "Delete from tedarikci where (kullanici_adi=?)";
            try {
                PreparedStatement stmet = (PreparedStatement) connection.prepareStatement(query);
                stmet.setString(1, tedarikci.getKullaniciAdi());
                stmet.executeUpdate();
                return false;
            } catch (SQLException e1) {
                return false;
            }

        }
     
        return true;
    }

    public List<Tedarikci> listeTedarikci() throws SQLException {
        String query = "SELECT idtedarikci, sirket_adi,adres_tel,websitesi,ilgili_kisi,kullanici_adi,eposta FROM  tedarikci";
        List<Tedarikci> TedarikciListesi = new ArrayList<Tedarikci>();

        try {
            Statement statement = this.connection.createStatement();
            ResultSet result = statement.executeQuery(query);
            while (result.next()) {
                Tedarikci tedarikci = new Tedarikci();
                tedarikci.setId(result.getInt("idtedarikci"));
                tedarikci.setSirketAdi(result.getString("sirket_adi"));
                tedarikci.setAdres(result.getString("adres_tel"));
                tedarikci.setWebSite(result.getString("websitesi"));
                tedarikci.setIlgiliKisi(result.getString("ilgili_kisi"));
                tedarikci.setKullaniciAdi(result.getString("kullanici_adi"));
                tedarikci.setEposta(result.getString("eposta"));
                TedarikciListesi.add(tedarikci);
            }
            return TedarikciListesi;
        } catch (SQLException e) {
            return TedarikciListesi;
        }


    }

    public Tedarikci getirTedarikci(String tedarikci_id) {
        String query = "SELECT idtedarikci, sirket_adi,adres_tel,websitesi,ilgili_kisi,kullanici_adi,eposta FROM  tedarikci where (idtedarikci=?)";
        Tedarikci tedarikci = new Tedarikci();
        try {
            PreparedStatement commit = this.connection.prepareStatement(query);
            commit.setString(1, tedarikci_id);
            ResultSet result = commit.executeQuery();
            tedarikci.setId(result.getInt("idtedarikci"));
            tedarikci.setSirketAdi(result.getString("sirket_adi"));
            tedarikci.setAdres(result.getString("adres_tel"));
            tedarikci.setWebSite(result.getString("websitesi"));
            tedarikci.setIlgiliKisi(result.getString("ilgili_kisi"));
            tedarikci.setKullaniciAdi(result.getString("kullanici_adi"));
            tedarikci.setEposta(result.getString("eposta"));
            return tedarikci;
        } catch (SQLException e) {
            return tedarikci;
        }
    }

    public boolean silTedarikci(Tedarikci tedarikci) {
        String query = "Delete from tedarikci where (idtedarikci=?)";
        try {
            PreparedStatement stmet = (PreparedStatement) connection.prepareStatement(query);
            stmet.setInt(1, tedarikci.getId());
            stmet.executeUpdate();
            return true;
        } catch (SQLException e) {
            return false;
        }
    }

    public boolean guncelleTedarikci(Tedarikci tedarikci) {
        try {
            String query = "UPDATE tedarikci SET  sirket_adi=?,adres_tel=?,websitesi=?,ilgili_kisi=? where idtedarikci =?";
            PreparedStatement stmt = connection.prepareStatement(query);
            stmt.setString(1, tedarikci.getSirketAdi());
            stmt.setString(2, tedarikci.getAdres());
            stmt.setString(3, tedarikci.getWebSite());
            stmt.setString(4, tedarikci.getIlgiliKisi());
            stmt.setInt(5, tedarikci.getId());
            stmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }

    }
            
          public String getir_Tedarikcinin_Satinalmacisi(int ted_id) {
        String ted=null;
              try {
           
              String query = "select s.sirket_adi ted_adi from sat_ted, satinalmaci s where ted_id=? and s.idsatinalmaci=sa_id ";
           
              PreparedStatement commit = this.connection.prepareStatement(query);
              commit.setInt(1, ted_id);
              ResultSet result = commit.executeQuery();
                     while(result.next())
                           ted =result.getString("ted_adi");
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
            return "Satınalmaci veritabanından alınamadı!";
        }
      
     return ted;
    }

           public boolean guncelleTedarikcinin_Satinalmacisi(int ted_id, String sat_id) {
        try {
            String query = "UPDATE sat_ted SET  sa_id=? where ted_id=?";
            PreparedStatement stmt = connection.prepareStatement(query);
            stmt.setString(1, sat_id);
            stmt.setInt(2,ted_id );
            stmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
             return false;
        }
        
            
    }
}
